from tkinter import *
from tkinter import filedialog
from tkinter import ttk
from tkinter import font as tkfont
from threading import Thread
from mini_tools.cjjtool import *
from mini_tools.myexcel import *
from mini_tools.mytools import *
from mini_tools.uitool import update_pod_daochu
from sql import sqlseverDB
from config import *
import tkinter.messagebox as messagebox
import openpyxl,time,concurrent.futures

class PodFra(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        DCMainFrame(self).pack(fill=X,padx=3,pady=3)


datatype=2
titlename='yntitle'
ordername='shopee_id_order'

class DCMainFrame(Frame):
    def __init__(self,master=None):
        Frame.__init__(self,master)
        Label(self, text='印尼虾皮POD大数据', fg="black", bg="yellow",font=("微软雅黑", 13)).pack(fill=X,padx=5)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.kwcountStr=StringVar()
        self.SKUStr=StringVar()
        self.SKUStr.set('正在加载SKU信息...')
        self.createWidgets()

    def createWidgets(self):


        self.cmb=mbDiv(self)
        f0=Frame(self)
        f0.pack(fill=X)

        lrf=Frame(f0)
        lrf.pack(side=LEFT,fill=Y,padx=2,pady=2)

        f00=Frame(lrf)
        f00.pack(fill=X,padx=2,pady=2)
        Label(f00, textvariable=self.kwcountStr,fg='#FF00FF').pack(side=LEFT, padx=2, pady=2)
        
        label_font = tkfont.Font(family="微软雅黑", size=10, weight="bold")

        f02=Frame(lrf)
        f02.pack(fill=X,padx=2,pady=2)

        Label(f02, text='设定价格:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        Label(f02, text='Rp',font=tkfont.Font(family="微软雅黑", size=8),fg="#666").pack(side=LEFT,pady=2)
        self.PriceIpt = Entry(f02,width=6)
        self.PriceIpt.pack(side=LEFT, padx=2, pady=2)
        pod_price=get_config('PODPrice')
        if not pod_price:
            pod_price=99000
            update_config({'PODPrice':pod_price})
        self.PriceIpt.insert(0, str(pod_price))
        self.pod_price=pod_price
        self.btn_catmb=Button(f02,text='刷新模版',command=lambda:Thread(target=self.update_mb_by_price).start(),fg='#ffffff',bg='green',width=10)
        self.btn_catmb.pack(side=LEFT, padx=2, pady=2)
        self.kwcountStr.set(f'正在获取价位为:{pod_price} 印尼POD产品数量...')
        Thread(target=self.getkwcount).start()
        

        Label(f02,text='选择工厂:').pack(side=LEFT, padx=2, pady=2)
        self.FacSelect = ttk.Combobox(f02,width=15,state='readonly')
        self.fac_name_to_skus=get_config('factorys')
        self.fac_name_user_chose=get_config('facName')
        if self.fac_name_to_skus:
            self.FacSelect['value']=list(self.fac_name_to_skus.keys())
            self.update_SKU_info()
        else:
            Thread(target=self.load_fac).start()
            self.FacSelect['value']=['正在加载']
        
        self.FacSelect.pack(side=LEFT, padx=2, pady=2)

        
        if self.fac_name_user_chose:
            if self.fac_name_user_chose in self.fac_name_to_skus:
                self.FacSelect.set(self.fac_name_user_chose)
            else:
                self.FacSelect.current(0)
        else:
            self.FacSelect.current(0)

        self.FacSelect.bind("<<ComboboxSelected>>", self.chang_fac)

        f03=Frame(lrf)
        f03.pack(fill=X,padx=2,pady=2)
        Label(f03, text='产品数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.EveIpt = Entry(f03,width=6)
    
        self.EveIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='表份数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.FileCountIpt = Entry(f03,width=6)
        self.FileCountIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='模版数:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.MbCountIpt = Entry(f03,width=6)
        self.MbCountIpt.pack(side=LEFT, padx=2, pady=2)
        Label(f03, text='授权码:',font=label_font).pack(side=LEFT, padx=2, pady=2)
        self.SqmIpt = Entry(f03,width=6)
        self.SqmIpt.pack(side=LEFT, padx=2, pady=2)
        self.SqmIpt.insert(0, 'cai5415')

        f04=Frame(lrf)
        f04.pack(fill=X,padx=2,pady=2)
        self.dcbtn=Button(f04,text='导出(印尼标题)',command=lambda:Thread(target=self.start_dc,args=(1,)).start(),fg='#ffffff',bg='green')
        self.dcbtn_hoodie=Button(f04,text='导出(卫衣)',command=lambda:Thread(target=self.start_dc,args=(2,)).start(),fg='#ffffff',bg='green')
        self.dcbtn.pack(side=LEFT, padx=2, pady=2)
        self.dcbtn_hoodie.pack(side=LEFT, padx=2, pady=2)
        Label(f04, textvariable=self.SKUStr,fg='#855E42',justify=LEFT).pack(side=LEFT, padx=2, pady=2)

        rf=Frame(f0)
        rf.pack(side=RIGHT,fill=Y)
        rollDiv(rf)

        f1=Frame(self)
        f1.pack(fill=X)
        FactoryDiv(f1,self).pack(fill=X,padx=3,pady=3)

    def getkwcount(self):
        
        while True:
            try:
                                
                rskw=self.ms.ExecQuery('select qcontent from qqc where qtype=?',('印尼语POD',))
                self.qqkws=[r[0] for r in rskw]
                rs1=self.ms.ExecQuery(f'''SELECT COUNT(itemid) from ProShopeePh where datatype={datatype} 
                                      and {titlename} is not null  and ispodcp=1''')
                rs2=self.ms.ExecQuery(f'''SELECT COUNT(itemid) from ProShopeePh as p where p.daochu>=0 and p.datatype={datatype} 
                                      and p.{titlename} is not null  and p.ispodcp=1
                                      and not exists(select 1 from YNPodPrice AS au WHERE au.ph_itemid = p.itemid AND au.daochu!=0 AND 
                                        au.price ={self.pod_price})
                                      ''')
                rs3=self.ms.ExecQuery(f'''SELECT COUNT(itemid) from ProShopeePh where daochu=-5 and datatype={datatype} 
                        and {titlename} is not null and ispodcp=1''')

                self.kwcountStr.set(f'价位:《{self.pod_price}》,{rs1[0][0]} 个,可导出:{rs2[0][0]} ,已导出:{rs1[0][0]-rs2[0][0]-rs3[0][0]},侵权:{rs3[0][0]}')
                self.load_fac()
            except Exception as e:
                print(f'设置POD产品信息错误 => {e}')
                pass
            time.sleep(10)
    
    def load_fac(self):
        
        rs=ms.ExecQuery('select fac_name,sku1,sku2 from YNPodFacSku')
        fac_name_to_skus={}

        for r in rs:
            fac_name,sku1,sku2=r
            if fac_name_to_skus.get(fac_name):
                fac_name_to_skus[fac_name].append([sku1,sku2])
            else:
                fac_name_to_skus[fac_name]=[[sku1,sku2]]
            
        fac_name_list=list(fac_name_to_skus.keys())
        self.FacSelect['value']=fac_name_list
        self.fac_name_to_skus=fac_name_to_skus
        
        
        if self.fac_name_user_chose:
            if self.fac_name_user_chose in fac_name_list:
                self.FacSelect.set(self.fac_name_user_chose)
            else:
                self.FacSelect.current(0)
            update_config({'factorys':fac_name_to_skus})
        else:
            if fac_name_list:
                update_config({'factorys':fac_name_to_skus,'facName':fac_name_list[0]})
                self.fac_name_user_chose=fac_name_list[0]
                self.FacSelect.current(0)

                self.update_SKU_info()

    def chang_fac(self,e):

        fac_name_cur_chose=self.FacSelect.get()
        print(f'切换到工厂:《{fac_name_cur_chose}》')
        self.fac_name_user_chose=fac_name_cur_chose
        self.update_SKU_info()
        update_config({'facName':self.fac_name_user_chose})

    def update_SKU_info(self):
    
 
        skus=self.fac_name_to_skus.get(self.fac_name_user_chose)
        if not skus:
            self.fac_name_user_chose=list(self.fac_name_to_skus.keys())[0]
            update_config({'facName':self.fac_name_user_chose})
            skus=list(self.fac_name_to_skus.values())[1]

        sku_list1=[]
        sku_list2=[]

        for sku1,sku2 in skus:
            if sku1 not in sku_list1:
                sku_list1.append(sku1)
            if sku2 not in sku_list2:
                sku_list2.append(sku2)
        sku_str1=' | '.join(sku_list1[:4])
        sku_str2=' | '.join(sku_list2[:2])
        self.SKUStr.set(f'SKU1: {sku_str1},共{len(sku_list1)}个\nSKU2: {sku_str2},共{len(sku_list2)}个')
        
    def get_rp(self,itemids):
        wstr=','.join(['?' for _ in itemids])
        sqlstr=f'select * from proshopeeph where itemid in ({wstr})'
        rp=ms.ExecQuerydict(sqlstr,itemids)
        new_itemids=[]
        new_rp=[]
        yjc_itemid={}
        for r in rp:

            itemid=r['itemid']
            yntile=r['yntitle']
            if yjc_itemid.get(itemid):
                if yjc_itemid[itemid]=='不侵权':
                    new_rp.append(r)
            else:
                jiaojikws=contains_word(yntile,self.qqkws)
                if jiaojikws:
                    kpara=list(jiaojikws)
                    wstr=','.join(['?' for _ in kpara])
                    aff1=self.ms.ExecNoQuery(f'update qqc set usecount=usecount+1 where qtype=? and qcontent in ({wstr})',['印尼语POD']+kpara)
                    aff2=self.ms.ExecNoQuery('update proshopeeph set daochu=-5 where itemid=?',(itemid,))
                    print(f'《proshopeeph》,itemid:{itemid},触发侵权词{jiaojikws}:{aff1},加入黑名单:{aff2}')
                    yjc_itemid[itemid]='侵权'
                else:
                    new_rp.append(r)
                    new_itemids.append(itemid)
                    yjc_itemid[itemid]='不侵权'


        return new_itemids,new_rp

    def start_dc(self,dc_type):

        smtys={
            '标题':f'{titlename}Rcode',
            '产品ID':'itemid',
            '详细参数':'attributes',
            'SKU图片网址':'skuImg',
            '价格':'skuPirce',
            'SKUID':'skuid',
            '描述':'pdec'
        }

        zjys={
            'Option for Variation 1':'sku1',
            'Option for Variation 2':'sku2',
        }

        if not self.EveIpt.get().strip() or not self.FileCountIpt.get().strip() or not self.MbCountIpt.get().strip():
            messagebox.showinfo('提示','请输入完整导出信息')
            return

        sqm=self.SqmIpt.get().strip()
        if sqm!='cai5415':
            messagebox.showinfo('提示','请输入正确的授权码')
            return
        
        pod_price=self.PriceIpt.get().strip()
        self.pod_price=int(pod_price)
        if not pod_price:
            messagebox.showinfo('提示','请输入导出价格')
            return
        
        if len(pod_price)<5 or not pod_price.isdigit():
            messagebox.showinfo('提示','请输入正确价格')
            return
        
        if not self.fac_name_to_skus:
            messagebox.showinfo('提示','请选择正确工厂')
            return
        cur_chose_fac_name=self.FacSelect.get()
        cur_chose_skus=self.fac_name_to_skus[cur_chose_fac_name]

        self.dcbtn.config(state=DISABLED) 
        pod_price=int(pod_price)
        if self.pod_price!=pod_price:
            update_config({'PODPrice':pod_price})
            self.pod_price=pod_price
            
        everycount=int(self.EveIpt.get().strip())
        filecount=int(self.FileCountIpt.get().strip())

        mbcount=int(self.MbCountIpt.get().strip())

        px=self.cmb.px
        print(f'筛选本次{px}可导出的模版ID...')
        where_str=''
        if px!='yndccount':
            where_str='and search_clicks>0' if px=='click_count' else 'and my_sold>0'
        wstr_hoodie=''
        if dc_type==2:
            wstr_hoodie='and mb1.did=10768'
        sqlstr2=f'''
                select top {mbcount} mb1.did,
                    (SELECT COUNT(itemid) from ProShopeePh as p where p.pmbid=mb1.did and p.{titlename} is not null and p.ispodcp=1
                    {where_str} 
                    and p.daochu>=0 and not exists(select 1 from YNPodPrice AS au WHERE au.ph_itemid = p.itemid AND au.daochu!=0 AND 
                    au.price ={pod_price}) 
                    ) as yndccount
                    from mb as mb1
                    where duid=4
                    {wstr_hoodie}
                    and dstate=2 
                    and dxs=1
                    and mb1.dtype={datatype}
                    order by yndccount desc
            '''
        rs=ms.ExecQuery(sqlstr2)
        print(f'共获取{len(rs)} 个MBid')

        need_pcount=everycount*filecount
        dcmr=f'导出结果_{getTimeStr()}'
        os.mkdir(dcmr)
        for mbid,yndccount in rs:
            pros=[]
            dc_itemids=[]
            
            while True:
                cur_need_pount=need_pcount-len(dc_itemids)
                if cur_need_pount==0:
                    break
                www_str=''
                if dc_itemids:
                    itemid_wstr=','.join([f"'{ccc}'" for ccc in dc_itemids])
                    www_str=f'and itemid not in ({itemid_wstr})'
                print(f'正在获取模板 {mbid} 的数据...')
                rs=ms.ExecQuery(f'''select top {cur_need_pount} itemid from proshopeeph as p
                                where pmbid=?  and {titlename} is not null and p.daochu>=0 and p.ispodcp=1 {www_str}
                                {where_str}
                                and not exists(select 1 from YNPodPrice AS au WHERE au.ph_itemid = p.itemid AND au.daochu!=0 AND 
                                au.price ={pod_price})
                                group by itemid
                                ''',
                                (mbid,))
                if len(rs)==0:
                    break
                print(f'模版:{mbid},需要导出 {cur_need_pount} 个')

                qknum=5
                with concurrent.futures.ThreadPoolExecutor(max_workers=20) as t0:

                    tasks=[]
                    for j in range(0, len(rs), qknum):
                        cpids=[r[0] for r in rs[j:j+qknum]]

                        tasks.append(t0.submit(self.get_rp,cpids))
                    
                    for t in concurrent.futures.as_completed(tasks):

                        cur_itemids,rp=t.result()
                        smt_rp=get_pod_pro(rp,cur_chose_skus)
                        pros.extend(smt_rp)
                        dc_itemids.extend(cur_itemids)

                        print(f'模版:{mbid},成功导出{len(smt_rp)}个产品,当前导出量:{len(dc_itemids)},剩余导出:{need_pcount-len(dc_itemids)}')

            print(f'模板id:{mbid},成功获取产品：{len(pros)},需标记pid:{len(dc_itemids)}')

            dc_exceldata(pros,mbid,everycount,filecount,1,1,dcmr,smtys,zjys,pod_price)
            aff2=0
            now_time=datetime.now()
            task_count_chang_dc=len(dc_itemids)
            print(f'导出完毕,正在修改:{task_count_chang_dc}产品状态...')
            with concurrent.futures.ThreadPoolExecutor(max_workers=20) as tt:

                tasks_up_dc=[tt.submit(update_pod_daochu,[now_time,pod_price,dc_itemid,1,cur_chose_fac_name]) for dc_itemid in dc_itemids]
                for t0 in concurrent.futures.as_completed(tasks_up_dc):
                    res_aff,res_itemid=t0.result()
                    task_count_chang_dc-=1
                    aff2+=res_aff
                    print(f'itemid:{res_itemid},修改状态:{res_aff},剩余更改状态:{task_count_chang_dc}/{len(dc_itemids)}')

            print(f'模版id:{mbid},成功标注:{aff2}个产品导出状态')
            print(f'模板id:{mbid},成功导出(印尼标题)\n')
        print(f'全部导出完毕(印尼标题)')
        messagebox.showinfo('提示',f'全部导出完毕(印尼标题)')
        self.dcbtn.config(state=NORMAL)

    def update_mb_by_price(self):
        
        pod_price=self.PriceIpt.get().strip()
        if not pod_price:
            messagebox.showinfo('提示','请输入导出价格')
            return
        
        if len(pod_price)<5 or not pod_price.isdigit():
            messagebox.showinfo('提示','请输入正确价格')
            return

        self.btn_catmb.config(state=DISABLED)
        pod_price=int(pod_price)
        if self.pod_price!=pod_price:
            update_config({'PODPrice':pod_price})
            self.pod_price=pod_price
        self.kwcountStr.set(f'正在获取价位为:{pod_price} 印尼POD产品数量...')
        self.cmb.pod_price=pod_price
        self.cmb.getmbData()
        self.btn_catmb.config(state=NORMAL) 
     
class mbDiv(Frame):
    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.ms=sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.pack(fill=X,pady=2,padx=2)
        self.createWidgets()

    def createWidgets(self):
        
        self.mbStr=StringVar()
        self.mbStr.set('正在获取模板数据...')
        f2=Frame(self)
        f2.pack(fill=X,padx=2,pady=2)
        Label(f2, textvariable=self.mbStr,fg='red').pack(side=LEFT, padx=2, pady=2)
        self.mindc=None
        Thread(target=self.getmbcount).start()

        pod_price=get_config('PODPrice')
        if not pod_price:
            pod_price=99000
        self.pod_price=pod_price
        self.firstjz=True
        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        columns = ("did","dpcount","ynCanDcpcount","click_count","sold_count","price")
        tree = ttk.Treeview(f0, show = "headings", columns = columns)
        tree.column("did", anchor = "center",width=125)
        tree.column("dpcount", anchor = "center",width=125)
        tree.column("ynCanDcpcount", anchor = "center",width=125)
        tree.column("click_count", anchor = "center",width=125)
        tree.column("sold_count", anchor = "center",width=125)
        tree.column("price", anchor = "center",width=125)


        tree.heading("did", text = "模板id")
        tree.heading("dpcount", text = "产品总数量")
        tree.heading("ynCanDcpcount", text = "可导出数量")
        tree.heading("click_count", text = "有流量数量")
        tree.heading("sold_count", text = "出单量")
        tree.heading("price", text = "价位")
        
        
        self.VScroll1 = Scrollbar(f0, orient='vertical', command=tree.yview)
        self.VScroll1.pack(side=RIGHT,fill=Y)
        tree.configure(yscrollcommand=self.VScroll1.set)
        tree.pack(padx=3)
        self.tree=tree

        f1=Frame(self)
        f1.pack(fill=X,padx=2,pady=2)

        self.btn_upydata=Button(f1,text='从流量表更新流量和销量',command=lambda:Thread(target=self.upydata).start(),fg='#ffffff',bg='green',width=20)
        self.btn_upydata.pack(side=LEFT, padx=2, pady=2)
        
        self.btn_show_ll=Button(f1,text='模版按流量排序',command=lambda:Thread(target=self.upmb,args=('click_count',)).start(),fg='#ffffff',bg='green',width=20)
        self.btn_show_ll.pack(side=LEFT, padx=2, pady=2)

        self.btn_show_xl=Button(f1,text='模版按销量排序',command=lambda:Thread(target=self.upmb,args=('sold_count',)).start(),fg='#ffffff',bg='green',width=20)
        self.btn_show_xl.pack(side=LEFT, padx=2, pady=2)
        self.px='yndccount'
        Thread(target=self.getmbData).start()

    def getmbcount(self):
 
        while True:
            try:

                rs0=self.ms.ExecQuery(f'select count(did) from mb where duid=4 and dtype={datatype} and dxs=1')
                rs1=self.ms.ExecQuery(f'select count(did) from mb where duid=4 and dstate=2 and dtype={datatype} and dxs=1')
                rs2=self.ms.ExecQuery(f'select count(did) from mb where duid=4 and dstate=2 and dpstatus=1 and dtype={datatype} and dxs=1')
                self.mbStr.set(f'共有 {rs0[0][0]} 套模板,{rs1[0][0]} 套模板已经完成,其中有数据模板有 {rs2[0][0]} 套')
                time.sleep(120)
            except Exception as e:
                print(e)

    def upydata(self):
        self.btn_upydata.config(state=DISABLED)
        aff=self.ms.call_procedure('UpdateSearchClicks')
        print(f'更新流量和销量行数:{aff}')
        messagebox.showinfo('提示',f'更新流量和销量行数:{aff}')
        self.upmb(px='click_count')
        self.btn_upydata.config(state=NORMAL)
    


    def ck_count(self,px):
        where_str='search_clicks>0' if px=='click_count' else 'my_sold>0'
        rs1=self.ms.ExecQuery(f'''select count(*) from proshopeeph as p where p.datatype=2 and daochu>=0 and {where_str}
                            and not exists(select 1 from YNPodPrice AS au 
                              WHERE au.ph_itemid = p.itemid AND au.daochu!=0 AND au.price ={self.pod_price})''')
        
        rs2=self.ms.ExecQuery(f'select count(*) from proshopeeph where datatype=2 and {where_str} and daochu>=0')
        px_not_dc_num=rs1[0][0]
        px_num=rs2[0][0]
        #print(px_not_dc_num,px_num)
        if px_num>0:
            if px_not_dc_num/px_num<=0.1:

                sqlStr=f'''update YNPodPrice set daochu=0 where price={self.pod_price} and ph_itemid in 
                            (select itemid from proshopeeph where datatype=2 and {where_str})'''
                aff=self.ms.ExecNoQuery(sqlStr)
                print(f'{px}导出方式数量不足,重置所有{px}的导出状态为0,共重置{aff}行')

    def upmb(self,px='yndccount'):
        self.btn_show_ll.config(state=DISABLED)
        self.btn_show_xl.config(state=DISABLED)

        
        print(f'按 {px} 排序')
        self.getmbData(px)
        if px!='yndccount':
            Thread(target=self.ck_count,args=(px,)).start()
        self.btn_show_ll.config(state=NORMAL)
        self.btn_show_xl.config(state=NORMAL)
    
    def getmbData(self,px='yndccount'):
        self.px=px
        print(f'正在加载价位:《{self.pod_price}》,模版列表...')
        if not self.firstjz:
            for _ in map(self.tree.delete, self.tree.get_children("")):
                pass
        else:
            self.firstjz=False

        where_str=''
        if px=='click_count':
            where_str='and search_clicks>0'
        if px=='sold_count':
            where_str='and my_sold>0'

        sqlstr=f'''
                select mb1.did,
                (SELECT COUNT(itemid) from ProShopeePh where pmbid=mb1.did  and {titlename} is not null and ispodcp=1 
                and daochu>=0) as yncount,
                (SELECT COUNT(itemid) from ProShopeePh as p where pmbid=mb1.did and {titlename} is not null and ispodcp=1 and daochu>=0
                {where_str}
                and not exists(select 1 from YNPodPrice AS au WHERE au.ph_itemid = p.itemid AND au.daochu!=0 AND au.price ={self.pod_price})
                ) as CanDcCount,
                (SELECT COUNT(itemid) from ProShopeePh where pmbid=mb1.did 
                and ispodcp=1 and daochu>=0 and search_clicks>0 ) as click_count,
                (SELECT COUNT(itemid) from ProShopeePh where pmbid=mb1.did
                and ispodcp=1 and daochu>=0 and my_sold>0) as sold_count,
                '{self.pod_price}'
                from mb as mb1 where duid=4 and dstate=2 and dtype={datatype} order by CanDcCount desc
            '''


        rs=ms.ExecQuery(sqlstr)
        i=1
        for v in rs:
            cv=list(v)
            cvalues=cv
            self.tree.insert('', i, values =cvalues)
            i += 1
        print(f'价位:《{self.pod_price}》,模版加载完毕...')

class rollDiv(Frame):

    def __init__(self,master=None):
        Frame.__init__(self, master)
        self.pack(fill=X,pady=2,padx=2)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt=StringVar()
        self.statustxt.set('未选择路径')
        self.createWidgets()
    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X,padx=2,pady=2)
        
        f00=Frame(f0)
        f00.pack(pady=5,fill=X,padx=5)
        Label(f00, text='回溯路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择回溯路径',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)


        f01=Frame(f0)
        f01.pack(fill=X,pady=2,padx=2)
        Label(f01, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f02=Frame(f0)
        f02.pack(fill=X,pady=2,padx=2)
        self.bkbtn=Button(f02,text='回溯当前价位所有产品状态',fg='#ffffff',bg='green',command=lambda:Thread(target=self.back_data).start())
        self.bkbtn.pack(side=LEFT,padx=2, pady=2)

    
    def choosefile(self):

        select_path=filedialog.askdirectory()
        self.DaoRuIpt.delete(0, END)
        self.DaoRuIpt.insert(0,select_path)
        self.fpath=select_path
        cfs=os.listdir(select_path)
        itemids_price=[]
        pro_count=0
        for fname in cfs:
            cfpath=f'{select_path}/{fname}'
            wb=openpyxl.load_workbook(cfpath)
            ws=wb['Template']
            id_col=4
            price_col=12
            for col in range(1,ws.max_column+1):
                if ws.cell(row=3,column=col).value.lower()=='parent sku':
                    id_col=col
                if ws.cell(row=3,column=col).value.lower()=='price':
                    price_col=col
                    break
            cur_price=int(float(ws.cell(row=7,column=price_col).value))
            cur_itemids=[]
            for row in range(7,ws.max_row+1):
                itemid=ws.cell(row=row,column=id_col).value
                cur_itemids.append(itemid)
                pro_count+=1
            itemids_price.append([fname,cur_itemids,cur_price])
        
        self.itemids_price=itemids_price
        self.statustxt.set(f'当前目录下共有 {pro_count} 个产品')
        
    def back_data(self):

        if not self.DaoRuIpt.get().strip():
            messagebox.showinfo('提示','请先选择目录')
            return
        askys=messagebox.askyesno('提示',f'确定回溯 {len(self.itemids_price)} 个文件的状态值')
        if not askys:
            return
        self.bkbtn.config(state=DISABLED)
        j=1
        for fname,itemids,cur_price in self.itemids_price:
            print(f'开始回溯第 {j}/{len(self.itemids_price)} 个文件:《{fname}》的导出状态...')

            itemidsStr=','.join([f"'{itemid}'" for itemid in itemids])
            sqlStr=f'update YNPodPrice set daochu=0 where price={cur_price} and ph_itemid in ({itemidsStr})'
            aff=self.ms.ExecNoQuery(sqlStr)

            print(f'第 {j}/{len(self.itemids_price)} 个文件:《{fname}》,成功回溯 {aff} 行')
            messagebox.showinfo('提示',f'第 {j}/{len(self.itemids_price)} 个文件:《{fname}》,成功回溯 {aff} 行')
            j+=1

        self.bkbtn.config(state=NORMAL)

class FactoryDiv(Frame):

    def __init__(self,master,DCfra):
        Frame.__init__(self, master)
        self.DCfra=DCfra
        self.pack(fill=X,pady=2,padx=2)
        self.ms = sqlseverDB('{SQL Server}', SQL_SERVER, 'SuMaiTongPol', SQL_USER, SQL_PWD)
        self.statustxt=StringVar()
        self.statustxt.set('未导入文件')
        self.createWidgets()
    def createWidgets(self):

        f0=Frame(self)
        f0.pack(fill=X)
        
        Label(f0, text='工厂信息导入', fg="white", bg="#007FFF",font=("微软雅黑", 13),width=50).pack(fill=X,padx=5)

        f00=Frame(f0)
        f00.pack(fill=X)
        Label(f00, text='工厂SKU路径:').pack(side=LEFT, padx=2, pady=2)
        self.DaoRuIpt = Entry(f00)
        self.DaoRuIpt.pack(side=LEFT, padx=2, pady=2)
        Button(f00,text='选择文件',fg='#ffffff',bg='green',command=self.choosefile).pack(side=LEFT,padx=2, pady=2)
        Label(f00, textvariable=self.statustxt,fg='red').pack(side=LEFT, padx=2, pady=2)

        f01=Frame(f0)
        f01.pack(fill=X)
        self.btn_dr= Button(f01,text='上传工厂信息',fg='#ffffff',bg='green',command=lambda:Thread(target=self.dr_fac_info).start())
        self.btn_dr.pack(side=LEFT,padx=2, pady=2)
        Button(f01,text='导出现有工厂',fg='#ffffff',bg='green',command=self.dc_mb).pack(side=LEFT,padx=2, pady=2)

    def choosefile(self):
        select_path=filedialog.askopenfilename()
        if select_path.endswith('.xlsx'):
            self.DaoRuIpt.delete(0, END)
            self.DaoRuIpt.insert(0,select_path)
            self.fpath=select_path
            wb=openpyxl.load_workbook(select_path)
            ws=wb[wb.sheetnames[0]]
            max_row= ws.max_row
            self.statustxt.set(f'当前共{max_row-1}行')
            
        else:
            messagebox.showerror('错误提示','请选择excel文件')
    
    def dr_fac_info(self):
        
        fac_path=self.DaoRuIpt.get().strip()
        if not fac_path:
            messagebox.showinfo('提示','请先导入工厂SKU文件')
            return
        
        pdatas=getDictDatasByEecel2(fac_path)
        print(pdatas)
        if len(pdatas)==0:
            messagebox.showinfo('提示','表格为空数据')
            return
        
        self.btn_dr.config(state=DISABLED)
        fac_name_to_info={}
        for pdata in pdatas:
            fac_name=pdata['工厂名字'].strip()
            sku1=pdata['sku1(用|分割比如红色|白色)'].strip()
            sku2=pdata['sku2(用|分割)'].strip()
            sku_name1=pdata['skuname1(不填写默认color)']
            sku_name2=pdata['skuname2(不填写默认size)']
            etitle=pdata['固定文字']
            if not sku_name1:
                sku_name1='color'
            if not sku_name2:
                sku_name2='size'
            sku_list1=sku1.split('|')
            sku_list2=sku2.split('|')
            for sku_value1 in sku_list1:
                for sku_value2 in sku_list2:
                    if fac_name_to_info.get(fac_name):
                        
                        fac_name_to_info[fac_name].append([fac_name,sku_value1,sku_value2,sku_name1,sku_name2,etitle])
                    else:
                        fac_name_to_info[fac_name]=[[fac_name,sku_value1,sku_value2,sku_name1,sku_name2,etitle]]


        time_str=datetime.now()
        added_fac_count=0
        for fac_name,fac_info in fac_name_to_info.items():
            
            rs= self.ms.ExecQuery('select id from YNPodFactory where name=?',(fac_name,))
            if len(rs)==0:
                added_fac_count+=self.ms.ExecNoQuery('insert YNPodFactory(name,etitle,upload_time) values(?,?,?)',(fac_name,etitle,time_str))
                rs= self.ms.ExecQuery('select id from YNPodFactory where name=?',(fac_name,))            

            else:
                self.ms.ExecNoQuery('update YNPodFactory set etitle=? where name=?',(etitle,fac_name))
                aff_del=self.ms.ExecNoQuery('delete from YNPodFacSku where fac_id=?',(rs[0][0],))
                print(f'工厂:{fac_name},删除旧sku:{aff_del}个')
            
            fac_id=rs[0][0]
            dr_datas=[data+[fac_id,time_str] for data in fac_info]
            time_str=datetime.now()
            aff_sku_added=ms.insert_datas(dr_datas,'YNPodFacSku',['fac_name','sku1','sku2','sku_name1','sku_name2','etitle','fac_id','upload_time'])
            print(f'工厂:{fac_name},新增sku,组合共:{aff_sku_added}个')

        self.DCfra.load_fac()
        print(f'全部导入完毕,共导入:{added_fac_count} 个工厂')
        messagebox.showinfo('提示',f'全部导入完毕,共导入:{added_fac_count} 个工厂')
        self.btn_dr.config(state=NORMAL)

            
    def dc_mb(self):

        if not messagebox.askyesno('提示','确认导出数据库所有工厂'):
            return
        tits=['工厂名字','sku1(用|分割比如红色|白色)','sku2(用|分割)','skuname1(不填写默认color)','skuname2(不填写默认size)','固定文字']
        rs=self.ms.ExecQuerydict('select * from YNPodFacSku')
        fdatas={}
        for r in rs:
            fac_name=r['fac_name']
            if fac_name in fdatas:
                if r['sku1'] not in fdatas[fac_name][1]:
                    fdatas[fac_name][1].append(r['sku1'])
                if r['sku2'] not in fdatas[fac_name][2]:
                    fdatas[fac_name][2].append(r['sku2'])
    
            else:
                fdatas[fac_name]=[fac_name,[r['sku1']],[r['sku2']],'','',r['etitle']]
    

        wb=openpyxl.Workbook()
        ws=wb[wb.sheetnames[0]]
        ws.append(tits)
        for v in fdatas.values():
            v[1]='|'.join(v[1])
            v[2]='|'.join(v[2])
            ws.append(v)
        wb.save('工厂信息表格模版.xlsx')

        messagebox.showinfo('提示',f'成功导出到:《工厂信息表格模版.xlsx》')

# import tkinter
# list_win=tkinter.Tk()
# list_win.wm_geometry("800x650")
# list_win.title('POD导出')
# PodFra(list_win).pack(fill=X,padx=3,pady=3)
# list_win.mainloop()